package com.hugoo.core.db;

import com.hugoo.core.db.Conn.DB_TYPE;
import com.hugoo.core.db.mo.Record;
import com.hugoo.core.db.mo.RecordSet;
import com.hugoo.core.util.TL;
import java.io.Writer;
import java.sql.*;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 *
 * @author Xinton
 */
public class ODC {

    protected static final Log log = LogFactory.getLog(ODC.class);
    private Connection con;
    private Statement stmt;
    private ResultSet rs;
    private DB_TYPE dbt;

    ODC(String inst) {
        this.con = Conn.conn(inst);
        dbt = Conn.dbt(inst);
    }

    public int[] excute(List<String> sql) throws Exception {
        long l = System.currentTimeMillis();
        try {
            con.setAutoCommit(false);
            stmt = con.createStatement();
            for (String s : sql) {
                log.info("语句--> " + s);
                stmt.addBatch(s);
            }
            int[] r = stmt.executeBatch();
            con.commit();
            log.info("成功执行批处理语句【" + sql.size() + "】条,共耗时" + (System.currentTimeMillis() - l) + "ms。");
            con.setAutoCommit(true);
            return r;
        } catch (Exception e) {
            log.warn("执行批处理失败，共影响语句【" + sql.size() + "】条,共耗时" + (System.currentTimeMillis() - l) + "ms。", e);
            try {
                con.rollback();
            } catch (SQLException ex) {
                log.info("rollback data fails!", ex);
            }
            throw new Exception("执行批处理SQL语句失败！【" + sql + "】");
        } finally {
            release();
        }
    }

    public int[] excute(String... sql) {
        long l = System.currentTimeMillis();
        try {
            con.setAutoCommit(false);
            stmt = con.createStatement();
            for (String s : sql) {
                log.info("语句--> " + s);
                stmt.addBatch(s);
            }
            int[] r = stmt.executeBatch();
            con.commit();
            log.info("成功执行批处理语句【" + sql.length + "】条,共影响【" + r[0] + "】条记录,共耗时" + (System.currentTimeMillis() - l) + "ms。");
            con.setAutoCommit(true);
            return r;
        } catch (Exception e) {
            log.warn("执行批处理失败，共影响语句【" + sql.length + "】条,共耗时" + (System.currentTimeMillis() - l) + "ms。", e);
            try {
                con.rollback();
            } catch (SQLException ex) {
                log.info("rollback data fails!", ex);
            }
        } finally {
            release();
        }
        return null;
    }

    public String queryAndRuPrtXml(String sql) {
        ResultSetMetaData rsmd;
        StringBuilder sb = new StringBuilder();
        try {
            stmt = con.createStatement();
            log.info("执行打印查询：" + sql);
            rs = stmt.executeQuery(sql);
            rsmd = (ResultSetMetaData) rs.getMetaData();

            sb.append("<xml xmlns:s=\"u\" xmlns:dt=\"u\" xmlns:rs=\"u\" xmlns:z=\"#R\">  ");
            sb.append("<s:Schema id=\"RowsetSchema\"> ");
            sb.append("<s:ElementType name=\"row\" content=\"eltOnly\" rs:updatable=\"true\"> ");
            //////////////
            int cc = rsmd.getColumnCount() + 1;
            String rsValue, dataType;

            for (int i = 1; i < cc; i++) {
                dataType = rsmd.getColumnClassName(i);
                if ("java.lang.String".equals(dataType) || "java.sql.Timestamp".equals(dataType)
                        || "java.lang.Double".equals(dataType) || "java.lang.Integer".equals(dataType)
                        || "java.lang.Boolean".equals(dataType) || "java.lang.Float".equals(dataType)) {
                    sb.append("<s:AttributeType name=\"").append(rsmd.getColumnName(i)).append("\" rs:number=\"").
                            append(String.valueOf(i)).append("\" rs:nullable=\"true\" rs:maydefer=\"true\" rs:writeunknown=\"true\" "
                            + "   rs:basecolumn=\"").append(rsmd.getColumnName(i)).append("\"> ");

                    if ("java.lang.String".equals(dataType)) {
                        sb.append("<s:datatype dt:type=\"string\" ");
                        sb.append(" dt:maxLength=\"").append(rsmd.getColumnDisplaySize(i)).append("\" /> ");
                    } else if ("java.sql.Timestamp".equals(dataType)) {
                        sb.append("<s:datatype dt:type=\"datetime\" ");
                        sb.append(" dt:maxLength=\"16\" /> ");
                    } else if ("java.lang.Double".equals(dataType)) {
                        sb.append("<s:datatype dt:type=\"float\" ");
                        sb.append(" dt:maxLength=\"8\" /> ");
                    } else if ("java.lang.Integer".equals(dataType)) {
                        sb.append("<s:datatype dt:type=\"int\" ");
                        sb.append(" dt:maxLength=\"4\" /> ");
                    } else if ("java.lang.Boolean".equals(dataType)) {
                        sb.append("<s:datatype dt:type=\"boolean\" ");
                        sb.append(" dt:maxLength=\"2\" /> ");
                    } else if ("java.lang.Float".equals(dataType)) {
                        sb.append("<s:datatype dt:type=\"r4\" ");
                        sb.append(" dt:maxLength=\"4\" /> ");
                    }
                    sb.append("</s:AttributeType> ");
                }
            }
            sb.append("</s:ElementType> ");
            sb.append("</s:Schema> ");

            sb.append("<rs:data> ");
            while (rs.next()) {
                sb.append("<z:row ");
                for (int i = 1; i < cc; i++) {
                    dataType = rsmd.getColumnClassName(i);
                    if ("java.lang.String".equals(dataType) || "java.sql.Timestamp".equals(dataType)
                            || "java.lang.Double".equals(dataType) || "java.lang.Integer".equals(dataType)
                            || "java.lang.Boolean".equals(dataType) || "java.lang.Float".equals(dataType)) {
                        rsValue = rs.getString(i);
                        if (rsValue == null) {
                            continue;
                        }
                        if ("java.sql.Timestamp".equals(dataType)) {
                            rsValue = rsValue.substring(0, 10) + "T" + rsValue.substring(11, 19);
                        }
                        sb.append(rsmd.getColumnName(i)).append("=\"").append(TL.escape(rsValue)).append("\" ");
                    }
                }
                sb.append(" /> ");
            }
            sb.append("</rs:data> ");
            sb.append("</xml>");

            return sb.toString();
        } catch (Exception e) {
            return "";
        } finally {
            release();
        }
    }

    public RecordSet<Record> query(String sql, int ps, int cp) {
        RecordSet<Record> set;
        String sqt = null;
        try {
            long t1 = System.currentTimeMillis(), t2, t3;
            int tt = 0;
            //数据大小查询与包装
            stmt = con.createStatement();
            if (ps > 0) {//本处提到分页
                sqt = "select to_char(count(1)) from (" + sql + ") x";
                rs = stmt.executeQuery(sqt);
                if (rs.next()) {
                    String lox = rs.getString(1);
                    tt = TL.parseInt(lox, 0);
                }
                TL.close(rs);
                int tp;
                if (tt < 1) {
                    log.info("执行查询【" + sql + "】未返回结果集，共花费时间（ms）：" + (System.currentTimeMillis() - t1));
                    return new RecordSet();
                } else {
                    tp = tt % ps == 0 ? tt / ps : (tt / ps + 1);
                    cp = cp > tp ? tp : cp;
                }
                t2 = System.currentTimeMillis();
                //结果集查询与包装
                sqt = getQuerySql(sql, cp, ps);
                rs = stmt.executeQuery(sqt);
                t3 = System.currentTimeMillis();
                //
                set = this.packResultset(rs, ps, cp, tt, tp);
                log.info("执行查询【" + sql + "】共【" + tt + "】条计录，本次返回【" + set.size() + "】条计录，各阶段花费时间【统计:"
                        + (t2 - t1) + "/查询:" + (t3 - t2) + "/包装:" + (System.currentTimeMillis() - t3) + "】");
            } else {
                sqt = sql;
                t2 = System.currentTimeMillis();
                //结果集查询与包装
                rs = stmt.executeQuery(sqt);
                t3 = System.currentTimeMillis();
                set = this.packResultset(rs);
                log.info("执行查询【" + sqt + "】共返回【" + set.size() + "】条计录，各阶段花费时间【查询:" + (t3 - t2) + "/包装:"
                        + (System.currentTimeMillis() - t3) + "】");
            }
            return set;
        } catch (Exception e) {
            log.info("Excute query【" + sqt + "】 error!", e);
        } finally {
            release();
        }
        return new RecordSet<Record>();
    }

    private String getQuerySql(String csql, int cp, int ps) {
        StringBuilder sb = new StringBuilder();
        switch (dbt) {
            case ORACLE:
                if (cp == 1) {//(cp - 1) * ps
                    sb.append("select * from (").append(csql).append(") where rownum <= ").append(cp * ps);
                } else {
                    sb.append("select * from (select row_.*,rownum rownum_ from (").append(csql).append(" ) row_ where rownum <= ").append(cp * ps).
                            append(") where rownum_> ").append((cp - 1) * ps);
                }
                break;
            case SQL_SERVER:
                if (cp == 1) {//(cp - 1) * ps
                    sb.append("select top ").append(cp * ps).append(" * from (").append(csql).append(") ");
                } else {
                    sb.append("select * from ( select row_number()over(order by tempcolumn)ROW__ID,* from (select top ").
                            append(cp * ps ).append(" tempcolumn=0,* from  (").
                            append(csql).append(" ) EPDC)t )tt where ROW__ID>").append((cp - 1) * ps);
                }
                break;
            case MYSQL:
               /* if (cp == 1) {//(cp - 1) * ps
                    sb.append("select * from (").append(csql).append(") where rownum <= ").append(cp * ps);
                } else {
                    sb.append("select * from (select row_.*,rownum rownum_ from (").append(csql).append(" ) row_ where rownum <= ").append(cp * ps).
                            append(") where rownum_> ").append((cp - 1) * ps);
                } */
                break;
        }

        return sb.toString();
    }

    private RecordSet<Record> packResultset(ResultSet rs, int nCommonPageSize, int nCurrentPage, int nTotalRecordCount, int tp) throws SQLException {
        RecordSet<Record> set = new RecordSet<Record>();
        //移到结果集最后一条, 取得记录总数
        set.cp = nCurrentPage;
        set.ps = nCommonPageSize;
        set.tt = nTotalRecordCount;
        set.tp = tp;

        return packResultset(rs, set);
    }

    private RecordSet<Record> packResultset(ResultSet rs) throws SQLException {
        return packResultset(rs, new RecordSet<Record>());
    }

    private RecordSet<Record> packResultset(ResultSet rs, RecordSet<Record> set) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();
        int nColumn = rsmd.getColumnCount();
        //移到结果集最后一条, 取得记录总数

        while (rs.next()) {
            Record record = new Record();
            for (int i = 0; i < nColumn; i++) {
                String strField = rsmd.getColumnName(i + 1).toUpperCase();
                record.put(strField, rs.getObject(i + 1));
            }
            set.add(record);
        }
        return set;
    }

    public void saveClob(String sql, String column, String value) {
        PreparedStatement s = null;
        try {
            con.setAutoCommit(false);
            s = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
            rs = s.executeQuery();
            if (rs.next()) {
                try {
                    Clob c = rs.getClob(column.toUpperCase());
                    Writer x = c.setCharacterStream(0);
                    if (TL.isEmpty(value)) {
                        x.write("");
                    } else {
                        x.write(value);
                    }
                    x.close();
                    con.commit();
                } catch (Exception e) {
                    con.rollback();
                    log.info("传入ＣＬＯＢ字段时，出现异常！ ", e);
                }
            }
            con.setAutoCommit(true);
        } catch (SQLException e) {
            log.info("\u5efa\u7acbStatement\u65f6\u9519\u8bef\uff1a ", e);
        } finally {
            release(con, s, rs);
        }
    }

    private void release(Object... obj) {
        TL.close(rs, stmt, con);
        con = null;
        stmt = null;
        rs = null;
    }
}
